
const express = require("express");
const router = express.Router();
const utils = require("../../utils/index.js");
const pools = require("../../utils/pools.js");
const xlsx = require("node-xlsx");
let fs = require("fs");
//用户管理
//查询
router.post("/getUserList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	a.id,
	a.realname,
	a.username,
	a.department_id AS departmentId,
	b.department_name AS departmentName,
	a.state,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime,
	a.updated_user AS updatedUser
FROM
	users a
	LEFT JOIN  department b
	ON a.department_id=b.id
WHERE
	a.state = 0`;
    sql=utils.setLike(sql,"a.realname",obj.realname);
    let {total}=await utils.getSumBySql({sql,res,req});
    sql+=` ORDER BY a.updated_time DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});
router.post("/getUserGroup", async (req, res) => {
    let sql = `SELECT  id,realname,username,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser FROM users where  state=0  `;
    sql+=` ORDER BY updated_time DESC`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result}));
});
//新增
router.post("/insertUser", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  users (realname,username,password,state,created_time,created_user, updated_time,updated_user,department_id) VALUES (?,?,?,0,?,?,?,?,?)`;
    await pools({sql,val:[obj.realname,obj.username,'Aa123456',new Date(),0,new Date(),0,obj.departmentId],res,req,run:false});
});
//删除
router.post("/deleteUser", async (req, res) => {
    let obj=req.body;
    let sql = `update users set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateUser", async (req, res) => {
    let obj=req.body;
    let sql = `update users set realname=? ,username=?,updated_time=?,updated_user=?,department_id=? WHERE id = ?; `;
    await pools({sql,val:[obj.realname,obj.username,new Date(),0,obj.departmentId,obj.id],res,req,run:false});
});


//部门管理
//查询
router.post("/getDepartmentList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,department_name as departmentName,remarks,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser FROM department where  state=0  `;
    sql=utils.setLike(sql,"department_name",obj.departmentName);
    let {total}=await utils.getSum({sql,name:"department",res,req});
    sql+=` ORDER BY updated_time DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});
//新增
router.post("/insertDepartment", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  department (department_name,remarks,state,created_time,created_user, updated_time,updated_user) VALUES (?,?,0,?,?,?,?)`;
    await pools({sql,val:[obj.departmentName,obj.remarks,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteDepartment", async (req, res) => {
    let obj=req.body;
    let sql = `update department set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateDepartment", async (req, res) => {
    let obj=req.body;
    let sql = `update department set department_name=? ,remarks=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.departmentName,obj.remarks,new Date(),0,obj.id],res,req,run:false});
});


//项目管理
//查询
router.post("/getProjectList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,cost_number as costNumber,project_name as projectName,device_type as deviceType,year,drive_box_type,devices_number,fixtures_channels,project_manager,software_main_manager,software_manager,charge_discharge_manager,visual_director,factory_situation,customer_situation,factory_fat_time,delivery_time,customer_fat_time,remarks,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser FROM projects where  state=0 `;
    sql=utils.setTwoLike(sql,"cost_number","project_name",obj.title);
	if(obj.year){
		sql=sql+" and year ='" +  obj.year+"'"
	}
	if(obj.department){
		sql=sql+" and department ='" +  obj.department+"'"
	}
    // let {total}=await utils.getSum({sql,name:"projects",res,req});
    sql+=` ORDER BY updated_time DESC`;
    // sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result }));
});
router.post("/getProjectById", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,department,cost_number as costNumber,project_name as projectName,device_type as deviceType,year,drive_box_type,devices_number,fixtures_channels,project_manager,software_main_manager,software_manager,charge_discharge_manager,visual_director,factory_situation,customer_situation,factory_fat_time,delivery_time,customer_fat_time,remarks,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser FROM projects where  state=0  and id=?`;
    let {result}=await pools({sql,val:[obj.id],res,req});
    res.send(utils.returnData({ data: result[0]}));
});
router.post("/getProjectGroup", async (req, res) => {
    let sql = `SELECT  id,cost_number as costNumber,CONCAT(cost_number, '/', project_name) AS projectName FROM projects where  state=0 `;
    sql+=` ORDER BY updated_time DESC`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result}));
});
//新增
router.post("/insertProject", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  projects (cost_number,project_name,remarks,state,created_time,created_user, updated_time,updated_user) VALUES (?,?,?,0,?,?,?,?)`;
    await pools({sql,val:[obj.costNumber,obj.projectName,obj.remarks,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteProject", async (req, res) => {
    let obj=req.body;
    let sql = `update projects set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateProject", async (req, res) => {
    let obj=req.body;
    let sql = `update projects set cost_number=?,project_name=? ,remarks=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.costNumber,obj.projectName,obj.remarks,new Date(),0,obj.id],res,req,run:false});
});

//工单等级配置
//查询
router.post("/getWorkOrderLevelList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,level_name as levelName,remarks,state,created_time as createdTime,created_user as createdUser,updated_time as updatedTime,updated_user as  updatedUser  FROM work_order_level where  state=0 `;
    sql=utils.setLike(sql,"level_name",obj.levelName);
    let {total}=await utils.getSum({sql,name:"work_order_level",res,req});
    sql+=` ORDER BY updated_time DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});

//新增
router.post("/insertWorkOrderLevel", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  work_order_level (level_name,remarks,state,created_time,created_user, updated_time,updated_user) VALUES (?,?,0,?,?,?,?)`;
    await pools({sql,val:[obj.levelName,obj.remarks,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteWorkOrderLevel", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order_level set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateWorkOrderLevel", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order_level set level_name=? ,remarks=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.levelName,obj.remarks,new Date(),0,obj.id],res,req,run:false});
});



//夹具机标准版代码开发合并情况
//查询
router.post("/getFixtureMachinesList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	a.id,
	a.function_type AS functionType,
	a.function_content AS functionContent,
	a.dev_user_id AS devUserId,
	b.realname AS devUserName,
	a.version_number AS versionNumber,
	a.is_merge AS isMerge,
	a.merge_sha1 AS mergeSha1,
	a.merge_user_id AS mergeUserId,
	c.realname AS mergeUserName,
	a.state,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime,
	a.updated_user AS updatedUser
FROM
	fixture_machines_standard_version  a  LEFT JOIN users b ON a.dev_user_id=b.id  LEFT JOIN users c ON a.merge_user_id=c.id
WHERE
	a.state = 0`;
    sql+=` ORDER BY a.updated_time DESC`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result }));
});

//新增
router.post("/insertFixtureMachines", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  fixture_machines_standard_version (function_type, function_content,dev_user_id,version_number,is_merge,merge_sha1,merge_user_id,state,created_time,created_user,updated_time,updated_user) VALUES (?, ?, ?, ?, ?, ?,?,0, ?,?,?,?)`;
    await pools({sql,val:[obj.functionType,obj.functionContent,obj.devUserId,obj.versionNumber,obj.isMerge,obj.mergeSha1,obj.mergeUserId,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteFixtureMachines", async (req, res) => {
    let obj=req.body;
    let sql = `update fixture_machines_standard_version set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateFixtureMachines", async (req, res) => {
    let obj=req.body;
    let sql = `update fixture_machines_standard_version set function_type=?, function_content=?,dev_user_id=?,version_number=?,is_merge=?,merge_sha1=?,merge_user_id=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.functionType,obj.functionContent,obj.devUserId,obj.versionNumber,obj.isMerge,obj.mergeSha1,obj.mergeUserId,new Date(),0,obj.id],res,req,run:false});
});


//单机类标准版代码合并情况：共合并*次
//查询
router.post("/getSingleMachineStandardList", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	a.id,
	a.function_type AS functionType,
	a.function_content AS functionContent,
	a.dev_user_id AS devUserId,
	b.realname AS devUserName,
	a.version_number AS versionNumber,
	a.is_merge AS isMerge,
	a.merge_sha1 AS mergeSha1,
	a.merge_user_id AS mergeUserId,
	c.realname AS mergeUserName,
	a.state,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime,
	a.updated_user AS updatedUser
FROM
	single_machine_standard_code  a  LEFT JOIN users b ON a.dev_user_id=b.id  LEFT JOIN users c ON a.merge_user_id=c.id
WHERE
	a.state = 0`;
    sql+=` ORDER BY a.updated_time DESC`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result }));
});

//新增
router.post("/insertSingleMachineStandard", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  single_machine_standard_code (function_type, function_content,dev_user_id,version_number,is_merge,merge_sha1,merge_user_id,state,created_time,created_user,updated_time,updated_user) VALUES (?, ?, ?, ?, ?, ?,?,0, ?,?,?,?)`;
    await pools({sql,val:[obj.functionType,obj.functionContent,obj.devUserId,obj.versionNumber,obj.isMerge,obj.mergeSha1,obj.mergeUserId,new Date(),0,new Date(),0],res,req,run:false});
});
//删除
router.post("/deleteSingleMachineStandard", async (req, res) => {
    let obj=req.body;
    let sql = `update single_machine_standard_code set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//编辑
router.post("/updateSingleMachineStandard", async (req, res) => {
    let obj=req.body;
    let sql = `update single_machine_standard_code set function_type=?, function_content=?,dev_user_id=?,version_number=?,is_merge=?,merge_sha1=?,merge_user_id=?,updated_time=?,updated_user=? WHERE id = ?; `;
    await pools({sql,val:[obj.functionType,obj.functionContent,obj.devUserId,obj.versionNumber,obj.isMerge,obj.mergeSha1,obj.mergeUserId,new Date(),0,obj.id],res,req,run:false});
});



//查询工单列表
router.post("/getWorkOrderListCustomerDemand", async (req, res) => {
	let obj=req.body;
    let sql = `SELECT
	a.id,
	b.cost_number AS costNumber,
	b.id AS projectId,
	b.project_name AS projectName,
	a.work_content AS workContent,
	a.is_customer_demand AS isCustomerDemand,
	a.work_order_issuance_time AS workOrderIssuanceTime,
	a.execut_user_id AS executUserId,
	d.realname AS executUserName,
	a.completion_status AS completionStatus,
	a.completion_time AS completionTime,
	a.work_level_id AS workLevelId,
	a.not_closing_reason AS notClosingReason,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime,
	a.updated_user AS updatedUser,
	a.department_id AS departmentId,
	c.department_name AS departmentName,
	a.expected_completion_time AS expectedCompletionTime,
	a.version_number AS versionNumber,
	a.is_sign AS isSign
FROM
	work_order a
	LEFT JOIN  projects b
	ON a.project_id=b.id
	LEFT JOIN  department c 
	ON a.department_id = c.id
	LEFT JOIN  users d
	ON a.execut_user_id = d.id
WHERE
	a.state = 0
	AND a.is_customer_demand = 1
	AND a.work_order_issuance_time >= "${obj.startDate}" AND a.work_order_issuance_time <= "${obj.endDate}"
ORDER BY
	a.updated_time DESC`;
    await pools({sql,val:[],res,req,run:false});
});
router.post("/getWorkOrderListByDepartId", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	a.id,
	b.cost_number AS costNumber,
	b.id AS projectId,
	b.project_name AS projectName,
	a.work_content AS workContent,
    a.department_id AS departmentId,
	a.is_customer_demand AS isCustomerDemand,
	a.work_order_issuance_time AS workOrderIssuanceTime,
	a.execut_user_id AS executUserId,
	d.realname AS executUserName,
	a.completion_status AS completionStatus,
	a.completion_time AS completionTime,
	a.work_level_id AS workLevelId,
	c.level_name AS levelName,
	a.not_closing_reason AS notClosingReason,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime
FROM
	work_order a
	LEFT JOIN  projects b
	ON a.project_id=b.id
	LEFT JOIN  work_order_level c
	ON a.work_level_id=c.id
	LEFT JOIN  users d
	ON a.execut_user_id = d.id
where a.state = 0  and a.department_id = "${obj.departmentId}"   AND work_order_issuance_time >= "${obj.startDate}" AND work_order_issuance_time <= "${obj.endDate}"`;
    sql=utils.setTwoLike(sql,"b.cost_number","b.project_name",obj.title);
	if(obj.state){
		sql +=` and a.completion_status = "${obj.state}"  `
	}
	let {total}=await utils.getSumBySql({sql,res,req});
    sql+=` ORDER BY a.updated_time DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});
//查询所有工单
router.post("/getAllWorkOrderListBy", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	a.id,
	b.cost_number AS costNumber,
	b.id AS projectId,
	b.project_name AS projectName,
	a.work_content AS workContent,
    a.department_id AS departmentId,
	a.is_customer_demand AS isCustomerDemand,
	a.work_order_issuance_time AS workOrderIssuanceTime,
	a.execut_user_id AS executUserId,
	d.realname AS executUserName,
	a.completion_status AS completionStatus,
	a.completion_time AS completionTime,
	a.work_level_id AS workLevelId,
	c.level_name AS levelName,
	a.not_closing_reason AS notClosingReason,
	a.created_time AS createdTime,
	a.created_user AS createdUser,
	a.updated_time AS updatedTime
FROM
	work_order a
	LEFT JOIN  projects b
	ON a.project_id=b.id
	LEFT JOIN  work_order_level c
	ON a.work_level_id=c.id
	LEFT JOIN  users d
	ON a.execut_user_id = d.id
where a.state = 0  `;
	if(obj.startDate){
		sql +=` AND a.work_order_issuance_time >= "${obj.startDate}" AND  a.work_order_issuance_time <= "${obj.endDate}"  `
	}
	if(obj.userId){
		sql +=` And a.execut_user_id = "${obj.userId}"   `
	}
    sql=utils.setTwoLike(sql,"b.cost_number","b.project_name",obj.title);
	if(obj.state){
		sql +=` and a.completion_status = "${obj.state}"  `
	}
    let {total}=await utils.getSumBySql({sql,res,req});
    sql+=` ORDER BY a.updated_time DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});
router.post("/getWorkOrderTotalByDepartId", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	(
		SELECT
			COUNT(1)
		FROM
			work_order
        WHERE
            state = 0
            AND department_id = "${obj.departmentId}"
	) AS totalNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
            state = 0
			AND completion_status = 2
            AND department_id = "${obj.departmentId}"
	) AS totalFinishNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			work_order_issuance_time >= "${obj.startDate}"
			AND work_order_issuance_time <= "${obj.endDate}"
            AND state = 0
            AND department_id = "${obj.departmentId}"
	) AS totalWeekNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			work_order_issuance_time >= "${obj.startDate}"
			AND work_order_issuance_time <= "${obj.endDate}"
			AND completion_status = 2
            AND state = 0
            AND department_id = "${obj.departmentId}"
	) AS totalWeekFinishNum`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result[0]}));
});
router.post("/getWorkOrderTotalByuserId", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	(
		SELECT
			COUNT(1)
		FROM
			work_order
        WHERE
            state = 0`
			if(obj.userId){
				sql +=` And execut_user_id = "${obj.userId}"   `
			}
			if(obj.startDate){
				sql +=` AND work_order_issuance_time >= "${obj.startDate}" AND  work_order_issuance_time <= "${obj.endDate}"  `
			}
			sql += `
	) AS totalNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
            state = 0
			AND completion_status = 2`
			if(obj.userId){
				sql +=` And execut_user_id = "${obj.userId}"   `
			}
			if(obj.startDate){
				sql +=` AND work_order_issuance_time >= "${obj.startDate}" AND  work_order_issuance_time <= "${obj.endDate}"  `
			}
			sql +=`
	) AS totalFinishNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			state = 0
			AND is_customer_demand = 1`
			if(obj.userId){
				sql +=` And execut_user_id = "${obj.userId}"   `
			}
			if(obj.startDate){
				sql +=` AND work_order_issuance_time >= "${obj.startDate}" AND  work_order_issuance_time <= "${obj.endDate}"  `
			}
	sql +=`
	) AS totalCustomerNum`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result[0]}));
});


//新增工单
router.post("/insertWorkOrderList", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO  work_order (project_id,work_content,is_customer_demand,work_order_issuance_time,execut_user_id,completion_status,completion_time,work_level_id, not_closing_reason,state,created_time,created_user,updated_time,updated_user,department_id) VALUES (?,?,?,?,?,?,?,?,?,0, ?,?,?,?,?)`;
    await pools({sql,val:[obj.projectId,obj.workContent,obj.isCustomerDemand,obj.workOrderIssuanceTime,obj.executUserId,obj.completionStatus,obj.completionTime,obj.workLevelId,obj.notClosingReason,new Date(),0,new Date(),0,obj.departmentId],res,req,run:false});
});
//编辑工单
router.post("/updateWorkOrder", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order  set project_id=?,work_content=?,is_customer_demand=?,work_order_issuance_time=?,execut_user_id=?,completion_status=?,completion_time=?,work_level_id=?, not_closing_reason=?,updated_time=?,updated_user=? WHERE id = ?;`;
    await pools({sql,val:[obj.projectId,obj.workContent,obj.isCustomerDemand,obj.workOrderIssuanceTime,obj.executUserId,obj.completionStatus,obj.completionTime,obj.workLevelId,obj.notClosingReason,new Date(),0,obj.id],res,req,run:false});
});
router.post("/updateWorkOrderNew", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order  set expected_completion_time=?,version_number=?,is_sign=?,updated_time=?,updated_user=? WHERE id = ?;`;
    await pools({sql,val:[obj.expectedCompletionTime,obj.versionNumber,obj.isSign,new Date(),0,obj.id],res,req,run:false});
});

//删除工单
router.post("/deleteWorkOrder", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order set state=2  WHERE id = ?;`;
    await pools({sql,val:[obj.id],res,req,run:false});
});
//完成工单
router.post("/finishWorkOrder", async (req, res) => {
    let obj=req.body;
    let sql = `update work_order set completion_status=2,completion_time=?  WHERE id = ?;`;
    await pools({sql,val:[new Date(),obj.id],res,req,run:false});
});

//bar数据
router.post("/getWorkOrderTotalBarAll", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			work_order_issuance_time < "${obj.startDate}"
			AND   ( completion_status = 1  or  (completion_time >= "${obj.startDate}"  AND completion_time <= "${obj.endDate}"  AND completion_status = 2 ))
         AND state = 0
	) AS totalWeekBeforeNoFinishAllNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			work_order_issuance_time >= "${obj.startDate}"
			AND work_order_issuance_time <= "${obj.endDate}"
            AND state = 0
	) AS totalWeekAllNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			completion_time >= "${obj.startDate}"
			AND completion_time <= "${obj.endDate}"
			AND completion_status = 2
            AND state = 0
	) AS totalWeekAllFinishNum`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result[0]}));
});
router.post("/getWorkOrderTotalBarByDepartmentId", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			work_order_issuance_time >= "${obj.startDate}"
			AND work_order_issuance_time <= "${obj.endDate}"
            AND state = 0
            AND department_id = "${obj.departmentId}"
	) AS totalWeekNum,
	(
		SELECT
			COUNT(1)
		FROM
			work_order
		WHERE
			completion_time >= "${obj.startDate}"
			AND completion_time <= "${obj.endDate}"
			AND completion_status = 2
            AND state = 0
            AND department_id = "${obj.departmentId}"
	) AS totalWeekFinishNum`;
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result[0]}));
});



//报表数据
//工单汇总表
router.post("/getfetchTableAllData", async (req, res) => {
	let obj=req.body;
    let sql = `SELECT
	b.cost_number AS costNumber,
	b.project_name AS projectName,
	a.work_content AS workContent,
	a.work_order_issuance_time AS workOrderIssuanceTime,
	d.realname AS executUserName,
	CASE a.completion_status
        WHEN 3 THEN '取消'
        WHEN 2 THEN '已完成'
        ELSE '未完成'
    END
	 AS completionStatus,
	e.level_name AS levelName,
	a.not_closing_reason AS notClosingReason,
	a.department_id AS departmentId
FROM
	work_order a
	LEFT JOIN  projects b
	ON a.project_id=b.id
	LEFT JOIN  users d
	ON a.execut_user_id = d.id
	LEFT JOIN  work_order_level e
	ON a.work_level_id=e.id
WHERE
	a.state = 0
	AND (completion_status = 1 OR  (completion_time >= "${obj.startDate}"
			AND completion_time <= "${obj.endDate}"
			AND completion_status = 2) )
ORDER BY
	a.work_order_issuance_time ASC`;
    await pools({sql,val:[],res,req,run:false});
});
//个人任务完成率报表
router.post("/getReporterPersonalPercent", async (req, res) => {
    let obj=req.body;
    let sql = `
	SELECT
	a.realname,
	a.department_id,
	SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
		) AS orderLost,
	SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
		) AS orderNew,
	(SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
		)+SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
		)) AS orderAll,
	SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
		) AS orderLostFinish,
	SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
		) AS orderNewFinish,
	(SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
		)+SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
		)) AS orderAllFinish,
		SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.is_customer_demand = 1, 1, 0)
		) AS orderCostomer,
	CONCAT(COALESCE(ROUND(
		(SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
		) / SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
		))*100,
		2
	),0.00), '%') AS lost_rate,
	CONCAT(COALESCE(ROUND(
		(SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
		) / SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
		) )*100,
		2
	),0.00), '%') AS new_rate,
	CONCAT(COALESCE(ROUND(
		((SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
		)+SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
		))/ (SUM(
			IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
		)+SUM(
			IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
		)))*100,
		2
	),0.00), '%') AS all_rate
FROM
	users a
	LEFT JOIN work_order b
	ON a.id = b.execut_user_id AND b.state=0
	WHERE a.state=0  
GROUP BY
	a.realname,a.department_id
	UNION ALL
	SELECT
	'合计',
	'all',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0
	UNION ALL
	SELECT
	'合计',
	'one',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0 AND b.department_id=1
UNION ALL
	SELECT
	'合计',
	'two',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0 AND b.department_id=2
	UNION ALL
	SELECT
	'合计',
	'three',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0 AND b.department_id=3
	UNION ALL
	SELECT
	'合计',
	'four',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0 AND b.department_id=4
	UNION ALL
	SELECT
	'合计',
	'five',
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 1,
			1,
			0
		)
	) AS orderLost,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderNew,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 1,
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}",
				1,
				0
			)
		)
	) AS orderAll,
	SUM(
		IF(
			b.work_order_issuance_time < "${obj.startDate}"
			AND b.completion_status = 2
			AND b.completion_time >= "${obj.startDate}"
			AND b.completion_time <= "${obj.endDate}",
			1,
			0
		)
	) AS orderLostFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.completion_status = 2,
			1,
			0
		)
	) AS orderNewFinish,
	(
		SUM(
			IF(
				b.work_order_issuance_time < "${obj.startDate}"
				AND b.completion_status = 2
				AND b.completion_time >= "${obj.startDate}"
				AND b.completion_time <= "${obj.endDate}",
				1,
				0
			)
		)+ SUM(
			IF(
				b.work_order_issuance_time >= "${obj.startDate}"
				AND b.work_order_issuance_time <= "${obj.endDate}"
				AND b.completion_status = 2,
				1,
				0
			)
		)
	) AS orderAllFinish,
	SUM(
		IF(
			b.work_order_issuance_time >= "${obj.startDate}"
			AND b.work_order_issuance_time <= "${obj.endDate}"
			AND b.is_customer_demand = 1,
			1,
			0
		)
	) AS orderCostomer,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 2
						AND b.completion_time >= "${obj.startDate}"
						AND b.completion_time <= "${obj.endDate}",
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time < "${obj.startDate}"
						AND b.completion_status = 1,
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS lost_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}"
						AND b.completion_status = 2,
						1,
						0
					)
				) / SUM(
					IF(
						b.work_order_issuance_time >= "${obj.startDate}"
						AND b.work_order_issuance_time <= "${obj.endDate}",
						1,
						0
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS new_rate,
	CONCAT(
	COALESCE(
		ROUND(
			(
				(
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 2
							AND b.completion_time >= "${obj.startDate}"
							AND b.completion_time <= "${obj.endDate}",
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}"
							AND b.completion_status = 2,
							1,
							0
						)
					)
				)/ (
					SUM(
						IF(
							b.work_order_issuance_time < "${obj.startDate}"
							AND b.completion_status = 1,
							1,
							0
						)
					)+ SUM(
						IF(
							b.work_order_issuance_time >= "${obj.startDate}"
							AND b.work_order_issuance_time <= "${obj.endDate}",
							1,
							0
						)
					)
				)
			)* 100,
			2
		),0.00),
		'%'
	) AS all_rate
FROM
	work_order b
WHERE
	b.state = 0 AND b.department_id=5
	`;
    let {result}=await pools({sql,res,req});

    res.send(utils.returnData({ data: result}));
});

router.post("/exportReporterPersonalPercent",async (req,res)=>{
	let obj=req.body;
    let sql = `
		SELECT
		a.realname,
		a.department_id,
		SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
			) AS orderLost,
		SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
			) AS orderNew,
		(SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
			)+SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
			)) AS orderAll,
		SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
			) AS orderLostFinish,
		SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
			) AS orderNewFinish,
		(SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
			)+SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
			)) AS orderAllFinish,
			SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.is_customer_demand = 1, 1, 0)
			) AS orderCostomer,
		CONCAT(ROUND(
			(SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
			) / SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
			))*100,
			2
		), '%') AS lost_rate,
		CONCAT(ROUND(
			(SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
			) / SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
			) )*100,
			2
		), '%') AS new_rate,
		CONCAT(ROUND(
			((SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"  and b.completion_status = 2 AND   b.completion_time >= "${obj.startDate}" AND b.completion_time <= "${obj.endDate}", 1, 0)
			)+SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}"  and b.completion_status = 2, 1, 0)
			))/ (SUM(
				IF(b.work_order_issuance_time < "${obj.startDate}"   and b.completion_status = 1, 1, 0)
			)+SUM(
				IF(b.work_order_issuance_time >= "${obj.startDate}" AND b.work_order_issuance_time <= "${obj.endDate}", 1, 0)
			)))*100,
			2
		), '%') AS all_rate
	FROM
		users a
		LEFT JOIN work_order b
		ON a.id = b.execut_user_id AND b.state=0
		WHERE a.state=0  
	GROUP BY
		a.realname,a.department_id;`;
    	let {result}=await pools({sql,res,req});
		this.reporterPersonalPercentData = utils.groupBy(result, 'department_id')
    const data = [
		["一部"]
		["姓名", "遗留工单数", "新工单数", "工单总数", "遗留工单完成数","新工单完成数", "总完成数", "本周客户需求数","遗留工单完成率","新工单完成率","总工单完成率"]
	];
	  let newTableArray = this.reporterPersonalPercentData[1].map(obj => delete obj.department_id && Object.values(obj));
	  newTableArray.unshift(...data);
	  console.log(21245414,newTableArray);
	  // wch 设置列宽
	  const sheetOptions = {
		'!merges':[{
			s: {r:0, c: 0}, 
			e: {r: 0, c: 10}
		  }],
		'!cols': [{wch: 20}, {wch: 20}, {wch: 20},{wch: 20}, {wch: 20}, {wch: 20},{wch: 20}, {wch: 20}, {wch: 20},{wch: 20}, {wch: 20}, {wch: 20}]
	};
	  // mySheetName 表名 data导出的数据  sheetOptions 是配置项
	  var buffer = xlsx.build([{ name: '个人任务完成率报表', data: newTableArray }], { sheetOptions }); 
	  res.setHeader(
		'Content-Type',
		'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
	  );
	  res.setHeader('Content-Disposition', 'attachment; filename=test.xlsx');
	  // 将 Excel 文件的二进制流数据返回给客户端
	  res.end(buffer, 'binary');
})
router.get("/test",async (req,res)=>{
    res.send(utils.returnData({data:'order133'}));
})


module.exports = router;
